package com.iplant.ams.serviceImpl.dao.dms.scada;

import com.iplant.ams.service.basicenum.dms.scada.DMSDeviceStatusEnum;
import com.iplant.ams.service.po.dms.DMSDeviceLedger;
import com.iplant.ams.service.po.dms.scada.alarm.DMSDeviceAlarm;
import com.iplant.ams.service.po.dms.scada.alarm.DMSDeviceAlarmFrequency;
import com.iplant.ams.service.po.dms.scada.alarm.DMSDeviceAlarmStatistics;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceStatistics;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceStatisticsInfo;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceStatus;
import com.iplant.ams.service.po.dms.scada.status.DMSDeviceStatusStatistics;
import com.iplant.ams.serviceImpl.dao.dms.DMSDeviceLedgerDAO;
import com.iplant.ams.serviceImpl.utils.DMSDeviceStatusEnumTool;
import com.iplant.base.po.OutResult;
import com.iplant.base.po.Pagination;
import com.iplant.base.po.enums.DBEnumType;
import com.iplant.base.po.enums.DBSchemas;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.general.CalendarTool;
import com.iplant.base.utils.general.Configuration;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.basicenum.stat.StatTypes;
import com.iplant.basic.service.po.bms.BMSEmployee;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;


@Slf4j
public class DMSDeviceStatusStatisticsDAO extends BaseDAO {

    private static DMSDeviceStatusStatisticsDAO Instance = null;

    private DMSDeviceStatusStatisticsDAO() {
        super("dms_device_hisstatus", DBEnumType.MySQL, DBSchemas.AMS);
        if (DevicePlanWorkHours <= 0)
            DevicePlanWorkHours = 8;

        mStatusList.clear();
        mStatusList.add(0);
        mStatusList.addAll(DMSDeviceStatusEnumTool.getInstance().Values);
    }


    public static int DevicePlanWorkHours = StringUtils.parseInt(Configuration.readConfigString("dms.plan.work.hours", "config/config"));

    public static DMSDeviceStatusStatisticsDAO getInstance() {
        if (Instance == null)
            Instance = new DMSDeviceStatusStatisticsDAO();
        return Instance;
    }

    public List<Integer> mStatusList = new ArrayList<Integer>();

    /// <summary>
    /// 默认工作时长  可以通过接口修改  可能需要一个界面修改每天工作时长
    /// </summary>

    public List<DMSDeviceStatusStatistics> DMS_SelectDeviceStatusStatisticsList(BMSEmployee wLoginUser, int wDeviceID, String wDeviceNo, String wDeviceName,
                                                                                String wAssetNo, int wDeviceType, int wModelID, int wFactoryID, int wWorkShopID,  int wAreaID,
                                                                                int wStatus, Boolean wIsCombine,  Calendar wStartTime, Calendar wEndTime, Pagination wPagination,
                                                                                OutResult<Integer> wErrorCode) {
        List<DMSDeviceStatusStatistics> wResult = new ArrayList<DMSDeviceStatusStatistics>();
        try {
            List<DMSDeviceStatus> wDMSDeviceStatusList = DMSDeviceStatusDAO.getInstance().DMS_CurrentDeviceStatusList(wLoginUser,
                   StringUtils.parseListArgs( wDeviceID), wDeviceNo, wDeviceName,StringUtils.parseListArgs( wAssetNo) ,
                    wDeviceType, wModelID, wFactoryID, wWorkShopID, wAreaID,  wStatus, CalendarTool.getBasicDate(),CalendarTool.getBasicDate(), wPagination, wErrorCode);


            List<Integer> wDeviceIDList = new ArrayList<Integer>();
            for (DMSDeviceStatus wDeviceStatus : wDMSDeviceStatusList) {
                wDeviceIDList.add(wDeviceStatus.DeviceID);
                wResult.add(new DMSDeviceStatusStatistics(wDeviceStatus));
            }

            //将当前状态和历史状态统计在一起
            if (wIsCombine) {
                this.DMS_SelectDeviceStatusStatisticsTime(wLoginUser, wResult, wStartTime, wEndTime, wErrorCode);

                this.DMS_SelectDeviceStatusDetailStatisticsTime(wLoginUser, wResult, wStartTime, wEndTime, wErrorCode);
            }
        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusStatisticsList", e);
        }
        return wResult;
    }

    private Map<Integer, Integer> DMS_SelectDeviceStatusDays(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, DMSDeviceStatusEnum wDeviceStatusEnum, Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, Integer> wResult = new HashMap<Integer, Integer>();
        try {
            if (wDeviceIDList != null && wDeviceIDList.size() > 0) {

                for (Integer wDevice : wDeviceIDList) {
                    if (wDevice <= 0)
                        continue;
                    if (wResult.containsKey(wDevice))
                        continue;

                    wResult.put(wDevice, 0);

                }
            }

            if (wDeviceStatusEnum.getValue() <= 0) {
                return wResult;
            }
            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }


            String wSQL = StringUtils.Format(" select p.DeviceID,COUNT(p.DeviceID) as DayCount  " +
                            " FROM ( select t.ID as DeviceID, date_format(t1.StartDate, ''%Y-%m-%d'') as WorkDate FROM {0}.{2} t1 " +
                            " inner  join {0}.dms_device_ledger t on t.AssetNo = t1.DeviceID  " +
                            " where ( :wDeviceIDList = '''' or t.ID IN({1})) AND t1.DeviceStatus=:wDeviceStatusEnum  " +
                            " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <= t1.UpdateDate) " +
                            " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndTime >= t1.StartDate)  " +
                            " group by t.ID, WorkDate) p group by p.DeviceID;  "
                    , getDBName(), wDeviceIDList.size() > 0 ? StringUtils.Join(",", wDeviceIDList) : "0",this.TableName);
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();
            wParamMap.put("wDeviceIDList", StringUtils.Join(",", wDeviceIDList));
            wParamMap.put("wDeviceStatusEnum", wDeviceStatusEnum.getValue());
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            int wDeviceID = 0;
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                if (!wResult.containsKey(wDeviceID))
                    wResult.put(wDeviceID, 0);

                wResult.put(wDeviceID, StringUtils.parseInt(wReader.get("DayCount")));

            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDays", e);
        }
        return wResult;

    }

    private Map<Integer, Map<Long, Integer>> DMS_SelectDeviceStatusDays(BMSEmployee wLoginUser, List<Integer> wDeviceIDList, int wStatType,
                                                                        DMSDeviceStatusEnum wDeviceStatusEnum, Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, Map<Long, Integer>> wResult = new HashMap<Integer, Map<Long, Integer>>();
        try {
            if (wDeviceIDList != null && wDeviceIDList.size() > 0) {

                for (Integer wDevice : wDeviceIDList) {
                    if (wDevice <= 0)
                        continue;
                    if (wResult.containsKey(wDevice))
                        continue;

                    wResult.put(wDevice, new HashMap<Long, Integer>());

                }
            }

            if (wDeviceStatusEnum.getValue() <= 0) {
                return wResult;
            }
            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult;
            }


            String wGroupType = "";

            switch (StatTypes.getEnumType(wStatType)) {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(p.WorkDayDate,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case Month:
                    wGroupType = " str_to_date( DATE_FORMAT(p.WorkDayDate,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(p.WorkDayDate),'-',(quarter(p.WorkDayDate) * 3)-2,'-1'),'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(p.WorkDayDate),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(p.WorkDayDate,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }


            String wSQL = StringUtils.Format(" select p.DeviceID,COUNT(p.DeviceID) as DayCount, {2}   as StatTypeDate " +
                            " FROM ( select t.ID as DeviceID, date_format(t1.StartDate, ''%Y-%m-%d'') as WorkDayDate FROM {0}.{3} t1 " +
                            " inner  join {0}.dms_device_ledger t on t.AssetNo = t1.DeviceID  " +
                            " where ( :wDeviceIDList = '''' or t.ID IN({1})) AND t1.DeviceStatus=:wDeviceStatusEnum  " +
                            " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <= t1.UpdateDate) " +
                            " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndTime >= t1.StartDate)  " +
                            " group by t.ID, WorkDayDate) p group by p.DeviceID,StatTypeDate ;  "
                    , getDBName(), wDeviceIDList.size() > 0 ? StringUtils.Join(",", wDeviceIDList) : "0", wGroupType,this.TableName);
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();
            wParamMap.put("wDeviceIDList", StringUtils.Join(",", wDeviceIDList));
            wParamMap.put("wDeviceStatusEnum", wDeviceStatusEnum.getValue());
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            int wDeviceID = 0;
            Calendar wStatTypeDate = CalendarTool.getBasicDate();
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                wStatTypeDate = StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                if (!wResult.containsKey(wDeviceID))
                    wResult.put(wDeviceID, new HashMap<Long, Integer>());
                if (!wResult.get(wDeviceID).containsKey(wStatTypeDate.getTimeInMillis())) {
                    wResult.get(wDeviceID).put(wStatTypeDate.getTimeInMillis(), 0);
                }
                wResult.get(wDeviceID).put(wStatTypeDate.getTimeInMillis(), StringUtils.parseInt(wReader.get("DayCount")));

            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDays", e);
        }
        return wResult;

    }

    private void DMS_SelectDeviceStatusStatisticsTime(BMSEmployee wLoginUser, List<DMSDeviceStatusStatistics> wDMSDeviceStatusStatisticsList,
                                                      Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {

        try {


            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return;
            }
            if (wDMSDeviceStatusStatisticsList == null || wDMSDeviceStatusStatisticsList.size() <= 0)
                return;

            wDMSDeviceStatusStatisticsList.removeIf(p -> p.DeviceID <= 0);

            List<Integer> wDeviceIDList = wDMSDeviceStatusStatisticsList.stream().map(p -> p.DeviceID).distinct().collect(Collectors.toList());
            if (wDeviceIDList.size() <= 0)
                return;


            StringBuilder wSB = new StringBuilder();
            String wColTemp = ", sum(case when p.DeviceStatus={0} then p.Duration else 0 end) as Status{0} ," +
                    " count(p.DeviceStatus={0} or NULL) as CountStatus{0}  ";
            for (int wStatus : mStatusList) {
                wSB.append(StringUtils.Format(wColTemp, wStatus));
            }


            String wSQL = StringUtils.Format(
                    " SELECT t.ID as DeviceID" + wSB.toString()
                            + " FROM {0}.dms_device_ledger t  "
                            + " left join {0}.{2} p on t.AssetNo=p.DeviceID  "
                            + " WHERE  t.ID IN( {1} )  "
                            + " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')   or :wStartTime <= p.UpdateDate) "
                            + " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')   or :wEndTime >= p.StartDate) group by t.ID  ;"
                    , getDBName(), StringUtils.Join(",", wDeviceIDList),this.TableName);
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() <= 0) {
                return;
            }

            Map<Integer, DMSDeviceStatusStatistics> wDMSDeviceStatusStatisticsDic =
                    wDMSDeviceStatusStatisticsList.stream().collect(Collectors.toMap(p -> p.DeviceID, p -> p, (o1, o2) -> o1));


            Map<Integer, Integer> wDeviceStatusDaysDic = this.DMS_SelectDeviceStatusDays(wLoginUser, wDeviceIDList, DMSDeviceStatusEnum.TurnOn, wStartTime, wEndTime, wErrorCode);


            int wDeviceID = 0;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                if (!wDMSDeviceStatusStatisticsDic.containsKey(wDeviceID))
                    continue;

                if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic == null) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic = new HashMap<String, Integer>();
                } else if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.size() > 0) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.clear();
                }

                if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic == null) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic = new HashMap<String, Integer>();
                } else if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.size() > 0) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.clear();
                }

                for (int wStatus : mStatusList) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.put(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)));

                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.put(wStatus + "", StringUtils.parseInt(wReader.get("CountStatus" + wStatus)));

                }

                if (wDeviceStatusDaysDic.containsKey(wDeviceID))
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).PlanDuration = wDeviceStatusDaysDic.get(wDeviceID) * (DevicePlanWorkHours > 0 ? DevicePlanWorkHours : 8);

            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusStatisticsTime", e);
        }
    }


    private void DMS_SelectDeviceStatusDetailStatisticsTime(BMSEmployee wLoginUser, List<DMSDeviceStatusStatistics> wDMSDeviceStatusStatisticsList,
                                                            Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {

        try {


            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return;
            }
            if (wDMSDeviceStatusStatisticsList == null || wDMSDeviceStatusStatisticsList.size() <= 0)
                return;

            wDMSDeviceStatusStatisticsList.removeIf(p -> p.DeviceID <= 0);

            List<Integer> wDeviceIDList =
                    wDMSDeviceStatusStatisticsList.stream().map(p -> p.DeviceID).distinct().collect(Collectors.toList());
            if (wDeviceIDList.size() == 0)
                return;


            StringBuilder wSB = new StringBuilder();
            String wColTemp = ", sum(case when p.DeviceStatus={0} then p.Duration else 0 end) as Status{0} ," +
                    " count(p.DeviceStatus={0} or NULL) as CountStatus{0} ";
            for (int wStatus : mStatusList) {
                wSB.append(StringUtils.Format(wColTemp, wStatus));
            }


            String wSQL = StringUtils.Format(
                    " SELECT t.ID as DeviceID" + wSB.toString()
                            + " FROM {0}.dms_device_ledger t  "
                            + " left join {0}.{2} p on t.AssetNo=p.DeviceID  "
                            + " WHERE  p.ID>0 AND t.ID IN( {1} )  "
                            + " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= p.UpdateDate) "
                            + " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')   or :wEndTime >= p.StartDate) group by t.ID  ;"
                    , getDBName(), StringUtils.Join(",", wDeviceIDList),this.TableName);
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() <= 0) {
                return;
            }

            Map<Integer, DMSDeviceStatusStatistics> wDMSDeviceStatusStatisticsDic =
                    wDMSDeviceStatusStatisticsList.stream().collect(Collectors.toMap(p -> p.DeviceID, p -> p, (o1, o2) -> o1));


            Map<Integer, Integer> wDeviceStatusDaysDic = this.DMS_SelectDeviceStatusDays(wLoginUser, wDeviceIDList, DMSDeviceStatusEnum.TurnOn, wStartTime, wEndTime, wErrorCode);


            int wDeviceID = 0;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                if (!wDMSDeviceStatusStatisticsDic.containsKey(wDeviceID))
                    continue;

                if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic == null) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic = new HashMap<String, Integer>();
                }


                if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic == null) {
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic = new HashMap<String, Integer>();
                }

                for (int wStatus : mStatusList) {
                    if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.containsKey(wStatus + "")) {
                        wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.merge(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)), Integer::sum);
                    } else {
                        wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusDurationDic.put(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)));
                    }
                    if (wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.containsKey(wStatus + "")) {
                        wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.merge(wStatus + "", StringUtils.parseInt(wReader.get("CountStatus" + wStatus)), Integer::sum);

                    } else {
                        wDMSDeviceStatusStatisticsDic.get(wDeviceID).StatusTimesDic.put(wStatus + "", StringUtils.parseInt(wReader.get("CountStatus" + wStatus)));

                    }

                }

                if (wDeviceStatusDaysDic.containsKey(wDeviceID))
                    wDMSDeviceStatusStatisticsDic.get(wDeviceID).PlanDuration = wDeviceStatusDaysDic.get(wDeviceID) * (DevicePlanWorkHours > 0 ? DevicePlanWorkHours : 8);

            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDetailStatisticsTime", e);
        }
    }


    public List<DMSDeviceStatistics> DMS_SelectDeviceStatusDetailStatisticsTime(BMSEmployee wLoginUser, int wLineID, List<Integer> wDeviceIDList, String wAssetNo, int wStatType,
                                                                                Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        Map<Integer, DMSDeviceStatistics> wResult = new HashMap<Integer, DMSDeviceStatistics>();
        try {
            Calendar wBaseTime = CalendarTool.getBasicDate();
            if (wStartTime.compareTo(wBaseTime) < 0)
                wStartTime = wBaseTime;
            if (wEndTime.compareTo(wBaseTime) < 0)
                wEndTime = wBaseTime;
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResult.values().stream().collect(Collectors.toList());
            }
            if ((wDeviceIDList == null || wDeviceIDList.size() == 0) && StringUtils.isEmpty(wAssetNo))
                return wResult.values().stream().collect(Collectors.toList());

            Map<Integer, DMSDeviceLedger> wDMSDeviceLedgerDic;
            if (wDeviceIDList.size() > 0) {

                wDeviceIDList.removeIf(p -> p <= 0);

                if (wDeviceIDList.size() == 0)
                    return wResult.values().stream().collect(Collectors.toList());

                List<DMSDeviceLedger> wDMSDeviceLedgerList = DMSDeviceLedgerDAO.getInstance().SelectPOList(wLoginUser, wDeviceIDList,null, wErrorCode);
                if (wErrorCode.Result != 0)
                    return wResult.values().stream().collect(Collectors.toList());
                if (wDMSDeviceLedgerList.size() <= 0)
                    return wResult.values().stream().collect(Collectors.toList());

                wDMSDeviceLedgerDic = wDMSDeviceLedgerList.stream().collect(Collectors.toMap(p -> p.ID, p -> p));
            } else {

                DMSDeviceLedger wDMSDeviceLedger = DMSDeviceLedgerDAO.getInstance().DMS_SelectDeviceLedger(wLoginUser, -1, "", wAssetNo, wErrorCode);
                if (wErrorCode.Result != 0 && wDMSDeviceLedger != null && wDMSDeviceLedger.ID > 0)
                    return wResult.values().stream().collect(Collectors.toList());

                wDMSDeviceLedgerDic = new HashMap<Integer, DMSDeviceLedger>();
                wDMSDeviceLedgerDic.put(wDMSDeviceLedger.ID, wDMSDeviceLedger);


                wDeviceIDList.add(wDMSDeviceLedger.ID);
            }


            StringBuilder wSB = new StringBuilder();
            String wColTemp = ", sum(case when p.DeviceStatus={0} then p.Duration else 0 end) as Status{0} ," +
                    " count(p.DeviceStatus={0} or NULL) as CountStatus{0} ";
            for (int wStatus : mStatusList) {
                wSB.append(StringUtils.Format(wColTemp, wStatus));
            }

            String wGroupType = "";

            switch (StatTypes.getEnumType(wStatType)) {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(p.StartDate,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case Month:
                    wGroupType = " str_to_date( DATE_FORMAT(p.StartDate,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(p.StartDate),'-',(quarter(p.StartDate) * 3)-2,'-1'),'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(p.StartDate),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(p.StartDate,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }


            String wSQL = StringUtils.Format(
                    " SELECT t.ID as DeviceID, {2} as StatTypeDate " + wSB.toString()
                            + " FROM {0}.dms_device_ledger t  "
                            + " left join {0}.{3} p on t.AssetNo=p.DeviceID  "
                            + " WHERE p.ID>0 AND t.ID IN ( {1} )  "
                            + " and ( :wStartTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartTime <= p.UpdateDate) "
                            + " and ( :wEndTime <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')   or :wEndTime >= p.StartDate) group by t.ID,StatTypeDate  ;"
                    , getDBName(), StringUtils.Join(",", wDeviceIDList), wGroupType,this.TableName);
            wSQL = this.DMLChange(wSQL);
            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            List<Map<String, Object>> wQueryResult = this.queryForList(wSQL, wParamMap);

            if (wQueryResult.size() <= 0) {
                return wResult.values().stream().collect(Collectors.toList());
            }

            Map<Integer, Map<Long, Integer>> wDeviceStatusDaysDic = this.DMS_SelectDeviceStatusDays(wLoginUser, wDeviceIDList,
                    wStatType, DMSDeviceStatusEnum.TurnOn, wStartTime, wEndTime, wErrorCode);


            int wDeviceID = 0;
            Calendar wStatTypeDate = CalendarTool.getBasicDate();

            DMSDeviceStatisticsInfo wDMSDeviceStatisticsInfo = null;
            // wReader\[\"(\w+)\"\]
            for (Map<String, Object> wReader : wQueryResult) {
                wDeviceID = StringUtils.parseInt(wReader.get("DeviceID"));
                wStatTypeDate = StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                if (!wDMSDeviceLedgerDic.containsKey(wDeviceID))
                    continue;


                if (!wResult.containsKey(wDeviceID)) {

                    wResult.put(wDeviceID, new DMSDeviceStatistics());

                    wResult.get(wDeviceID).DeviceID = wDeviceID;
                    wResult.get(wDeviceID).StatType = wStatType;
                    wResult.get(wDeviceID).StatStartDate = wStartTime;
                    wResult.get(wDeviceID).StatEndDate = wEndTime;
                    wResult.get(wDeviceID).DeviceNo = wDMSDeviceLedgerDic.get(wDeviceID).Code;
                    wResult.get(wDeviceID).DeviceName = wDMSDeviceLedgerDic.get(wDeviceID).Name;
                    wResult.get(wDeviceID).AssetNo = wDMSDeviceLedgerDic.get(wDeviceID).AssetNo;
                    wResult.get(wDeviceID).ModelID = wDMSDeviceLedgerDic.get(wDeviceID).ModelID;
                    wResult.get(wDeviceID).ModelName = wDMSDeviceLedgerDic.get(wDeviceID).StandardModelName;
                    wResult.get(wDeviceID).ModelNo = wDMSDeviceLedgerDic.get(wDeviceID).StandardModelCode;
                    wResult.get(wDeviceID).Remark = wDMSDeviceLedgerDic.get(wDeviceID).Description;


                }


                wDMSDeviceStatisticsInfo = new DMSDeviceStatisticsInfo();
                wDMSDeviceStatisticsInfo.DeviceID = wDeviceID;
                wDMSDeviceStatisticsInfo.StatStartDate = wStatTypeDate;
                wDMSDeviceStatisticsInfo.StatEndDate = wStatTypeDate;
                wDMSDeviceStatisticsInfo.DeviceNo = wDMSDeviceLedgerDic.get(wDeviceID).Code;
                wDMSDeviceStatisticsInfo.DeviceName = wDMSDeviceLedgerDic.get(wDeviceID).Name;
                wDMSDeviceStatisticsInfo.PartInterval = wDMSDeviceLedgerDic.get(wDeviceID).PartInterval;
                wDMSDeviceStatisticsInfo.AssetNo = wDMSDeviceLedgerDic.get(wDeviceID).AssetNo;
                wDMSDeviceStatisticsInfo.ModelID = wDMSDeviceLedgerDic.get(wDeviceID).ModelID;
                wDMSDeviceStatisticsInfo.ModelName = wDMSDeviceLedgerDic.get(wDeviceID).StandardModelName;
                wDMSDeviceStatisticsInfo.ModelNo = wDMSDeviceLedgerDic.get(wDeviceID).StandardModelCode;
                wDMSDeviceStatisticsInfo.Remark = wDMSDeviceLedgerDic.get(wDeviceID).Description;


                switch (StatTypes.getEnumType(wStatType)) {
                    case Week:
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.DAY_OF_MONTH,7);
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MILLISECOND,-1); 
                        break;
                    case Month:
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MONTH,1);
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MILLISECOND,-1);
                        
                        break;
                    case Quarter:
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MONTH,3);
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MILLISECOND,-1);
                        break;
                    case Year:
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.YEAR,1);
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MILLISECOND,-1);
                        break;
                    default:
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.DAY_OF_MONTH,1);
                        wDMSDeviceStatisticsInfo.StatEndDate.add(Calendar.MILLISECOND,-1);
                        break;
                }


                for (int wStatus : mStatusList) {
                    wDMSDeviceStatisticsInfo.StatusDurationDic.put(wStatus + "", StringUtils.parseInt(wReader.get("Status" + wStatus)));

                    wDMSDeviceStatisticsInfo.StatusTimesDic.put(wStatus + "", StringUtils.parseInt(wReader.get("CountStatus" + wStatus)));

                }

                if (wDeviceStatusDaysDic.containsKey(wDeviceID) && wDeviceStatusDaysDic.get(wDeviceID).containsKey(wStatTypeDate.getTimeInMillis())) {
                    wDMSDeviceStatisticsInfo.PlanDuration = wDeviceStatusDaysDic.get(wDeviceID).get(wStatTypeDate.getTimeInMillis()) * (DevicePlanWorkHours > 0 ? DevicePlanWorkHours : 8) * 3600;
                }

                if (wResult.get(wDeviceID).StatisticsInfoList == null) {
                    wResult.get(wDeviceID).StatisticsInfoList = new ArrayList<DMSDeviceStatisticsInfo>();
                }

                wResult.get(wDeviceID).StatisticsInfoList.add(wDMSDeviceStatisticsInfo);
            }

        } catch (Exception e) {

            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("DMS_SelectDeviceStatusDetailStatisticsTime", e);
        }
        return wResult.values().stream().collect(Collectors.toList());
    }


}
